-- Function to update invoice balance
CREATE OR REPLACE FUNCTION update_invoice_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update invoice timestamp
CREATE TRIGGER trigger_update_invoice_timestamp
BEFORE UPDATE ON invoices
FOR EACH ROW
EXECUTE FUNCTION update_invoice_timestamp();

-- Function to check room availability
CREATE OR REPLACE FUNCTION check_room_availability(p_room_number VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
    v_status VARCHAR(20);
BEGIN
    SELECT status INTO v_status
    FROM rooms
    WHERE room_number = p_room_number;
    
    RETURN (v_status = 'available');
END;
$$ LANGUAGE plpgsql;

-- Function to get patient age
CREATE OR REPLACE FUNCTION get_patient_age(p_patient_id INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_dob DATE;
    v_age INTEGER;
BEGIN
    SELECT date_of_birth INTO v_dob
    FROM patients
    WHERE patient_id = p_patient_id;
    
    v_age = EXTRACT(YEAR FROM AGE(CURRENT_DATE, v_dob));
    RETURN v_age;
END;
$$ LANGUAGE plpgsql;

-- Function to get total appointments by staff
CREATE OR REPLACE FUNCTION get_staff_appointment_count(p_staff_id VARCHAR, p_start_date DATE, p_end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM appointments
    WHERE staff_id = p_staff_id
    AND appointment_date BETWEEN p_start_date AND p_end_date
    AND status NOT IN ('cancelled', 'no_show');
    
    RETURN COALESCE(v_count, 0);
END;
$$ LANGUAGE plpgsql;

-- Function to calculate total revenue for a period
CREATE OR REPLACE FUNCTION calculate_revenue(p_start_date DATE, p_end_date DATE)
RETURNS DECIMAL(10,2) AS $$
DECLARE
    v_revenue DECIMAL(10,2);
BEGIN
    SELECT COALESCE(SUM(total_amount), 0) INTO v_revenue
    FROM invoices
    WHERE invoice_date BETWEEN p_start_date AND p_end_date
    AND status = 'paid';
    
    RETURN v_revenue;
END;
$$ LANGUAGE plpgsql;

-- Function to check medication stock
CREATE OR REPLACE FUNCTION check_medication_stock()
RETURNS TABLE(medication_name VARCHAR, current_stock INTEGER, reorder_level INTEGER) AS $$
BEGIN
    RETURN QUERY
    SELECT m.medication_name, m.stock_quantity, m.reorder_level
    FROM medications m
    WHERE m.stock_quantity <= m.reorder_level
    ORDER BY m.stock_quantity ASC;
END;
$$ LANGUAGE plpgsql;

-- Function to get patient's upcoming appointments
CREATE OR REPLACE FUNCTION get_upcoming_appointments(p_patient_id INTEGER)
RETURNS TABLE(
    appointment_id INTEGER,
    staff_name VARCHAR,
    appointment_date DATE,
    appointment_time TIME,
    status VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.appointment_id,
        ms.first_name || ' ' || ms.last_name AS staff_name,
        a.appointment_date,
        a.appointment_time,
        a.status
    FROM appointments a
    JOIN medical_staff ms ON a.staff_id = ms.staff_id
    WHERE a.patient_id = p_patient_id
    AND a.appointment_date >= CURRENT_DATE
    AND a.status IN ('scheduled', 'confirmed')
    ORDER BY a.appointment_date, a.appointment_time;
END;
$$ LANGUAGE plpgsql;

-- Function to prevent double booking
CREATE OR REPLACE FUNCTION prevent_double_booking()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM appointments
        WHERE staff_id = NEW.staff_id
        AND appointment_date = NEW.appointment_date
        AND appointment_time = NEW.appointment_time
        AND appointment_id != COALESCE(NEW.appointment_id, -1)
        AND status NOT IN ('cancelled', 'no_show')
    ) THEN
        RAISE EXCEPTION 'Staff member already has an appointment at this time';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to prevent double booking
CREATE TRIGGER trigger_prevent_double_booking
BEFORE INSERT OR UPDATE ON appointments
FOR EACH ROW
EXECUTE FUNCTION prevent_double_booking();

-- Function to automatically update room status
CREATE OR REPLACE FUNCTION update_room_status()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.patient_id IS NOT NULL AND OLD.patient_id IS NULL THEN
        NEW.status = 'occupied';
        NEW.admission_date = CURRENT_DATE;
    ELSIF NEW.patient_id IS NULL AND OLD.patient_id IS NOT NULL THEN
        NEW.status = 'available';
        NEW.discharge_date = CURRENT_DATE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update room status when patient is assigned/removed
CREATE TRIGGER trigger_update_room_status
BEFORE UPDATE ON rooms
FOR EACH ROW
EXECUTE FUNCTION update_room_status();

-- Function to get overdue invoices
CREATE OR REPLACE FUNCTION get_overdue_invoices()
RETURNS TABLE(
    invoice_id INTEGER,
    patient_name VARCHAR,
    total_amount DECIMAL,
    due_date DATE,
    days_overdue INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        i.invoice_id,
        p.first_name || ' ' || p.last_name AS patient_name,
        i.total_amount,
        i.due_date,
        (CURRENT_DATE - i.due_date)::INTEGER AS days_overdue
    FROM invoices i
    JOIN patients p ON i.patient_id = p.patient_id
    WHERE i.status IN ('pending', 'partial')
    AND i.due_date < CURRENT_DATE
    ORDER BY days_overdue DESC;
END;
$$ LANGUAGE plpgsql;

-- Function to calculate patient's total outstanding balance
CREATE OR REPLACE FUNCTION get_patient_balance(p_patient_id INTEGER)
RETURNS DECIMAL(10,2) AS $$
DECLARE
    v_balance DECIMAL(10,2);
BEGIN
    SELECT COALESCE(SUM(balance_due), 0) INTO v_balance
    FROM invoices
    WHERE patient_id = p_patient_id
    AND status IN ('pending', 'partial');
    
    RETURN v_balance;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION update_invoice_timestamp() IS 'Automatically updates timestamp when invoice is modified';
COMMENT ON FUNCTION check_room_availability(VARCHAR) IS 'Checks if a room is available for assignment';
COMMENT ON FUNCTION get_patient_age(INTEGER) IS 'Calculates patient age from date of birth';
COMMENT ON FUNCTION prevent_double_booking() IS 'Prevents scheduling conflicts for medical staff';
COMMENT ON FUNCTION get_overdue_invoices() IS 'Returns list of overdue invoices with patient details';

